
CREATE  trigger tt_billlay_au
on dbo.tt_billlay
for update
as  
begin
  set nocount on
  declare @old_layid integer,  
          @new_layid integer,            
          @old_depotid integer,
          @new_depotid integer,
          @old_productid integer,
          @new_productid integer,
          @old_quantity decimal(18,4),
          @new_quantity decimal(18,4),
          @old_assqty decimal(18,4),
          @new_assqty decimal(18,4),
          @old_tid integer,
          @new_tid integer          
  declare ins_cursor cursor local forward_only optimistic for
    select tid, layid, bquantity, depotid, productid, assqty from inserted
  declare del_cursor cursor local forward_only optimistic for  
    select tid, layid, bquantity, depotid, productid, assqty from deleted
  
  open ins_cursor
  open del_cursor  
  fetch next from ins_cursor into @new_tid, @new_layid, @new_quantity, @new_depotid, @new_productid, @new_assqty
  fetch next from del_cursor into @old_tid, @old_layid, @old_quantity, @old_depotid, @old_productid, @old_assqty

  while (@@fetch_status = 0)
  begin
    update tt_booklay 
    set beginqty = beginqty + @new_quantity - @old_quantity,
      beginassqty = beginassqty + @new_assqty - @old_assqty,
      assqty = assqty + @new_assqty  - @old_assqty,
      quantity = quantity + @new_quantity - @old_quantity, 
      productid = @new_productid, depotid = @new_depotid, layid = @new_layid
    where productid = @old_productid and depotid = @old_depotid and layid = @old_layid  
    
    fetch next from ins_cursor into @new_tid, @new_layid, @new_quantity, @new_depotid, @new_productid, @new_assqty
    fetch next from del_cursor into @old_tid, @old_layid, @old_quantity, @old_depotid, @old_productid, @old_assqty
  end
  
  close ins_cursor
  close del_cursor
  deallocate ins_cursor
  deallocate del_cursor  
end

